create or replace type body m4o_fn as
    constructor function m4o_fn (
        p_c in number )
        return self as result
    is
    begin
        self.c := p_c;
        return;
    end;
    constructor function m4o_fn (
        p_sql in varchar2,
        p_n1  in varchar2 default null,
        p_v1  in varchar2 default null,
        p_n2  in varchar2 default null,
        p_v2  in varchar2 default null,
        p_n3  in varchar2 default null,
        p_v3  in varchar2 default null )
        return self as result
    is
    begin
        c := dbms_sql.open_cursor;
        dbms_sql.parse(
            c,
            --'declare me m4o_fn := m4o_fn(p_c=>:me); begin '||
            'declare me number := :me; begin '||
            p_sql||
            'end;',
            dbms_sql.native);
        self.bind('me',self.c);
        if p_n1 is not null then
            self.bind(p_n1,p_v1);
        end if;
        if p_n2 is not null then
            self.bind(p_n2,p_v2);
        end if;
        if p_n3 is not null then
            self.bind(p_n3,p_v3);
        end if;
        return;
    end;
    member procedure bind (
        self in m4o_fn,
        p_n in varchar2,
        p_v in number )
    is
    begin
        dbms_sql.bind_variable(self.c,p_n,p_v);
    end;
    member procedure bind (
        self in m4o_fn,
        p_n in varchar2,
        p_v in varchar2 )
    is
    begin
        dbms_sql.bind_variable(self.c,p_n,p_v,32767);
    end;
    member procedure bind (
        self in m4o_fn,
        p_n in varchar2,
        p_v in date )
    is
    begin
        dbms_sql.bind_variable(self.c,p_n,p_v);
    end;
    member procedure bind_null (
        self in m4o_fn,
        p_n in varchar2 )
    is
        l_null varchar2(32767);
    begin
        self.bind(p_n,l_null);
    end;
    member procedure exec (
        self in m4o_fn )
    is
        l_num_processed number;
    begin
        l_num_processed := dbms_sql.execute(c);
    end;
    member function exec (
        self     in m4o_fn,
        p_n1     in varchar2 default null,
        p_v1     in varchar2 default null,
        p_n2     in varchar2 default null,
        p_v2     in varchar2 default null,
        p_n3     in varchar2 default null,
        p_v3     in varchar2 default null,
        p_result in varchar2 default 'r')
        return varchar2
    is
        l_null          varchar2(32767);
        l_value         varchar2(32767);
        l_num_processed number;
    begin
        if self.bound_result_name is null
           or self.bound_result_name <> p_result
        then
            dbms_sql.bind_variable(self.c,p_result,l_null,32767);
        end if;
        --self.bind_null(p_result);
        if p_n1 is not null then
            self.bind(p_n1,p_v1);
        end if;
        if p_n2 is not null then
            self.bind(p_n2,p_v2);
        end if;
        if p_n3 is not null then
            self.bind(p_n3,p_v3);
        end if;
        l_num_processed := dbms_sql.execute(c);
        dbms_sql.variable_value(self.c,p_result,l_value);
        return l_value;
    end;
    member function value (
        self in m4o_fn,
        p_n in varchar2 )
        return varchar2
    is
        l_val varchar2(32767);
    begin
        dbms_sql.variable_value(self.c,p_n,l_val);
        return l_val;
    end;
    member procedure close (
        self in m4o_fn )
    is
        l_c number := self.c;
    begin
        dbms_sql.close_cursor(l_c);
    end;
end;
/
show err

